var queues = require('mysql-queues');
const DEBUG = true;

var selectByKey = function(connection, keyword, time,levSta, rules, callback) {
	var sqlStr = "";
	var sqlStr2 = "";

	var departId = "";

	var sql3 = "";
	var level = "";
	console.log("levSta:",levSta);
	console.log("keyword:",keyword);
	if(levSta == "未审核") {
		sql3 = sql3 +" and p7.state in (0,3)";
	}else if(levSta == "未确认") {
		sql3 = sql3 +" and p7.state = 1";
	}else if(levSta == "已确认") {
		sql3 = sql3 +" and p7.state = 2";
	}else{
		level = levSta;
	}
	var sql = "select p7.* from (select p6.* from (select p5.*, o2.orgName projectOrg from (select p4.employeeName, p4.lastChangeName lastChange, p4.superior, p4.score, p4.level, p4.comment, p4.project, p4.preDeptId, p4.departmentId,p4.perfDate, p4.state, p4.id perId, o.orgName from (select p3.*, e3.name superior from (select p2.*, e2.name lastChangeName from (select p.* , e.name employeeName from performance p left join employee e on p.employeeId = e.id)p2 left join employee e2 on p2.lastChange = e2.id)p3 left join employee e3 on p3.assessmentPersonId = e3.id)p4 left join organization o on p4.departmentId = o.id)p5 left join organization o2 on p5.preDeptId = o2.id)p6 where p6.employeeName like ? or p6.project like ? or p6.orgName like ? or p6.projectOrg like ?)p7 where p7.perfDate like ? and p7.level like ?" +sql3;

	if (rules.length > 0) {
		if (rules[0][0] == 'departmentId') {
			if (rules[0][1].length > 0) {
				for (var j = rules[0][1].length - 1; j >= 0; j--) {
					if (j == 0) {
						departId = departId + rules[0][1][j].id;
					} else {
						departId = departId + rules[0][1][j].id + ",";
					}
				}
			} else {
				departId = "0000";
			}

			sqlStr = sqlStr + " and p.departmentId in(" + departId + ") ";
		} else if (rules[0][0] == 'area') {
			sqlStr2 = sqlStr2 + " and o.orgAddress='" + rules[0][1].orgAddress + "' ";
		}

		// console.log('sqlStr:',sqlStr);
		sql = "select p7.* from (select p6.* from (select p5.*, o2.orgName projectOrg from (select p4.employeeName, p4.lastChangeName lastChange, p4.superior, p4.score, p4.level, p4.comment, p4.project, p4.preDeptId,p4.departmentId, p4.perfDate, p4.state, p4.id perId, o.orgName from (select p3.*, e3.name superior from (select p2.*, e2.name lastChangeName from (select p.* , e.name employeeName from performance p left join employee e on p.employeeId = e.id where 1=1 " + sqlStr + " )p2 left join employee e2 on p2.lastChange = e2.id)p3 left join employee e3 on p3.assessmentPersonId = e3.id)p4 left join organization o on p4.departmentId = o.id  where 1=1 " + sqlStr2 + " )p5 left join organization o2 on p5.preDeptId = o2.id)p6 where p6.employeeName like ? or p6.project like ? or p6.orgName like ? or p6.projectOrg like ?)p7 where p7.perfDate like ? and p7.level like ?" +sql3 ;

	}
	console.log("level:",level);
	connection.query(sql, ['%' + keyword + '%', '%' + keyword + '%', '%' + keyword + '%', '%' + keyword + '%', '%' + time + '%' , '%' + level + '%'], function(err, rows) {
		// console.log("selectByKey--rows:", rows);
		if (err){
			console.log(err);
			console.log(err.stack);
		}
		callback(rows);
		connection.release();
	});
	// var sql='select em.name as superior,e2.perfDate,e2.state, e2.id as perId,e1.orgName,e2.orgName as projectOrg,e2.project,e1.name as employeeName,e2.score,e2.name as lastChange,e2.level,e2.comment from (select o.orgName,e.name,e.id from employee e,organization o where e.departmentId=o.id) e1,(select p.state,o.orgName,p.project,p.id,p.assessmentPersonId,p.perfDate,p.score,e.name,p.level,p.comment,p.employeeId from performance p,employee e,organization o where p.lastChange=e.id and p.preDeptId=o.id) e2,employee em where (e1.name like ? or e2.name like ? or e1.orgName like ? or e2.level like ?) and e2.employeeId=e1.id and em.id=e2.assessmentPersonId and e2.perfDate=?';
	// connection.query(sql,['%'+keyword+'%','%'+keyword+'%','%'+keyword+'%','%'+keyword+'%',time],function(err, rows){
	// 	console.log('err:',err);
	// 	console.log('rows:',rows);
	// 	if(err){
	// 		callback(err);
	// 		return;
	// 	}
	// 	callback(rows);
	// 	connection.release();
	// });
},
	//统计每个部门的优秀员工数
	selARatio = function(connection, time, rules, callback) {
		var sqlStr = "";
		var sqlStr2 = "";
		var departId = "";
		if (rules.length > 0) {
			if (rules[0][0] == 'departmentId') {
				if (rules[0][1].length > 0) {
					for (var j = rules[0][1].length - 1; j >= 0; j--) {
						if (j == 0) {
							departId = departId + rules[0][1][j].id;
						} else {
							departId = departId + rules[0][1][j].id + ",";
						}
					}
				} else {
					departId = "0000";
				}

				sqlStr = sqlStr + " and e3.departmentId in(" + departId + ") ";
			} else if (rules[0][0] == 'area') {
				sqlStr2 = sqlStr2 + " and o2.orgAddress='" + rules[0][1].orgAddress + "' ";
			}

			console.log('sqlStr:', sqlStr);
			console.log('sqlStr2:', sqlStr2);
		}

		var sql = "select e3.*, o2.orgName, o2.orgAddress from (select e2.*, p3.num from (select e.departmentId ,e.workPlace, count(e.id) total from employee e group by e.departmentId)e2 left join (select p2.departmentId perfDept, count(p2.employeeId)num from (select p.employeeId, e.departmentId from performance p left join employee e on p.employeeId = e.id where p.perfDate like ? and p.level = 'A')p2 group by p2.departmentId)p3 on e2.departmentId = p3.perfDept)e3 left join organization o2 on e3.departmentId = o2.id where 1=1" + sqlStr + sqlStr2;
		connection.query(sql, '%' + time + '%', function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			} else {
				callback(rows);
			}
			connection.release();
		});

	},
	//统计年度的A/D次数超过两次的员工id
	analyzePerf = function(connection, year, type, callback) {
		var sql = "";
		if(type == 'A') {
			sql = "select p3.*, o.orgName, o.orgAddress from (select p2.*, e.name, e.departmentId from (select p.employeeId, count(p.id)num from performance p where p.level = '"+type+"' and p.perfDate like '"+year+"%' group by p.employeeId)p2 left join employee e on p2.employeeId = e.id where p2.num >= 2)p3 left join organization o on p3.departmentId = o.id";
		}else{
			sql = "select p3.*, o.orgName, o.orgAddress from (select p2.*, e.name, e.departmentId from (select p.employeeId, count(p.id)num from performance p where p.level in('D', 'E') and p.perfDate like '"+year+"%' and p.state = 2 group by p.employeeId)p2 left join employee e on p2.employeeId = e.id where p2.num >= 2)p3 left join organization o on p3.departmentId = o.id";
		}
		// var sql = "select p3.*, o.orgName, o.orgAddress from (select p2.*, e.name, e.departmentId from (select p.employeeId, count(p.id)num from performance p where p.level = ? and p.perfDate like '"+year+"%' group by p.employeeId)p2 left join employee e on p2.employeeId = e.id where p2.num >= 2)p3 left join organization o on p3.departmentId = o.id";
		connection.query(sql, function(err, rows) {
			if(err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			}else{
				callback(rows);
			}
			connection.release();
		});
	},
	//根据员工id查询员工绩效等级为A/D月份
	analyzeDetails = function(connection, employeeId, year, type, callback) {
		var sql = "";
		if(type == 'A') {
			sql = "select p.perfDate, p.level from performance p where p.employeeId = ? and p.perfDate like '"+year+"%' and p.level = '"+type+"'";
		}else{
			sql = "select p.perfDate, p.level from performance p where p.employeeId = ? and p.perfDate like '"+year+"%' and p.level in ('D', 'E')";
		}
		// var sql = "select p.perfDate from performance p where p.employeeId = ? and p.perfDate like '"+year+"%' and p.level = '"+type+"'";
		connection.query(sql, employeeId, function(err, rows) {
			if(err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			}else{
				callback(rows);
			}
			connection.release();
		});
	},


	//统计绩效未填写及未审核员工
	statisPerf = function(connection, time, rules, callback) {
		var sqlStr = "";
		var sqlStr2 = "";
		var departId = "";
		var callbackData = {};
		if (rules.length > 0) {
			if (rules[0][0] == 'departmentId') {
				if (rules[0][1].length > 0) {
					for (var j = rules[0][1].length - 1; j >= 0; j--) {
						if (j == 0) {
							departId = departId + rules[0][1][j].id;
						} else {
							departId = departId + rules[0][1][j].id + ",";
						}
					}
				} else {
					departId = "0000";
				}

				sqlStr = sqlStr + " and e.departmentId in(" + departId + ") ";
			} else if (rules[0][0] == 'area') {
				sqlStr2 = sqlStr2 + " and o.orgAddress='" + rules[0][1].orgAddress + "' ";
			}

			console.log('sqlStr:', sqlStr);
			console.log('sqlStr2:', sqlStr2);

		}
		var sql = "select e.*,o.orgName, o.orgAddress from employee e left join organization o on e.departmentId = o.id where 1=1 and e.id not in (select p.employeeId from performance p where p.perfDate like ?)  and e.state = '正式'" + sqlStr + sqlStr2 + "order by e.departmentId";
		// var sql2 = "select t.* from(select count(*)num, e.departmentId from employee e left join performance p on p.employeeId = e.id where 1=1 and p.state in (0, 3) and p.perfDate like ?" + sqlStr + ")t left join organization o on t.departmentId = o.id where 1=1" + sqlStr2;
		var sql2 = "select count(*)num from (select t.* from (select p.* from performance p left join employee e on p.employeeId = e.id where 1=1 and p.state in (0,3) and p.perfDate like ?" + sqlStr + ")t left join organization o on t.departmentId = o.id where 1=1" + sqlStr2 + ")t2";
		connection.query(sql, '%' + time + '%', function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			} else {
				// callback(rows);
				callbackData.unwrite = rows;
				connection.query(sql2, '%' + time + '%', function(err, sum) {
					callbackData.uncheck = sum;
					callback(callbackData);
				})
			}
			connection.release();
		});
	};

module.exports = {
	selectByKey: selectByKey,
	statisPerf: statisPerf,
	selARatio: selARatio,
	analyzePerf: analyzePerf,
	analyzeDetails: analyzeDetails
};